﻿using MyBBSWebApi.DAL.Core;
using MyBBSWebApi.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace MyBBSWebApi.DAL
{
    public class UserDal
    {
        //返回库中所有用户数据-封装后的
        public List<Users> GetAll()
        {
            DataTable res = SqlHelper.ExecuteTable("SELECT * FROM users");
            List<Users> userList = ToModelList(res);
            return userList;
        }

        //对通过id获取数据的访问数据层进行封装
        public Users GetUserById(int id)
        {
            DataRow dr = null;
            DataTable res = SqlHelper.ExecuteTable("SELECT * FROM users WHERE id = @id", new SqlParameter("@id", id));
            if (res.Rows.Count > 0)
            {
                dr = res.Rows[0];
            }
            Users user = ToModel(dr);
            return user;
        }

        //对获取对应用户名和密码是否正确的访问数据层进行封装
        public List<Users> GetUserByusernoAndpassword(string userno, string password)
        {
            DataTable res = SqlHelper.ExecuteTable(
                "SELECT * FROM users WHERE userno = @userno AND password = @password",
                new SqlParameter("@userno", userno),
                new SqlParameter("@password", password));

            /*//为进行ToModelList封装前的代码
            List<Users> userList = new List<Users>();   //列表的创建，创建一个Users对象的列表 userlist
            for (int i = 0; i < res.Rows.Count; i++)
            {
                DataRow row = res.Rows[i];
                userList.Add(new Users
                {
                    Id = (int)row["id"],
                    Userno = row["userno"].ToString(),
                    Username = row["username"].ToString(),
                    Userlevel = (int)row["userlevel"],
                    Password = row["password"].ToString()
                });
            }*/

            //使用ToModelList进行封装后的代码
            List<Users> userList = ToModelList(res);
            return userList;
        }


        //对插入数据insert行为的访问数据层进行封装
        public int AddUser(int id, string userno, string username, int userlevel, string password)
        {
            return SqlHelper.ExecuteNonQuery(
                "INSERT INTO users(id, userno, username, userlevel, password) VALUES (@id, @userno, @username, @userlevel, @password)",
                new SqlParameter("@id", id),
                new SqlParameter("@userno", userno),
                new SqlParameter("@username", username),
                new SqlParameter("@userlevel", userlevel),
                new SqlParameter("password", password));
            
        }

        //对更新所需数据update行为的访问数据层进行封装
        public int UpdateUser(int id, string userno, string username, int? userlevel, string password)
        {
            DataTable res = SqlHelper.ExecuteTable("SELECT * FROM users WHERE id = @id", new SqlParameter("@id", id));
            int rowCount = 0;
            if (res.Rows.Count > 0)
            {
                DataRow row = res.Rows[0];
                Users user = new Users();
                user.Id = (int)row["id"];
                user.Userno = userno ?? row["userno"].ToString();
                user.Username = username ?? row["username"].ToString();
                user.Userlevel = userlevel ?? (int)row["userlevel"];
                user.Password = password ?? row["password"].ToString();
                rowCount = SqlHelper.ExecuteNonQuery(
                    "UPDATE users Set userno = @userno, username = @username, userlevel = @userlevel, password = @password WHERE id = @id",
                    new SqlParameter("@userno", user.Userno),
                    new SqlParameter("@username", user.Username),
                    new SqlParameter("@userlevel", user.Userlevel),
                    new SqlParameter("@password", user.Password),
                    new SqlParameter("@id", user.Id));
            }
            return rowCount;
        }

        //对删除数据remove行为的访问数据层进行封装
        public int RemoveUser(int id, string userno, string username)
        {
            return SqlHelper.ExecuteNonQuery(
                "DELETE FROM users WHERE id = @id AND userno = @userno AND username = @username",
                new SqlParameter("@id", id),
                new SqlParameter("@userno", userno),
                new SqlParameter("@username", username));
        }

        //按照ID进行数据删除
        public int RemoveID(int id)
        {
            return SqlHelper.ExecuteNonQuery(
                "DELETE FROM users WHERE id = @id",
                new SqlParameter("@id", id));
        }


        //对模型映射向列表中添加Users对象进行封装为私有方法ToModelList
        private List<Users> ToModelList(DataTable table)
        {
            List<Users> userList = new List<Users>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                Users user = ToModel(row);
                userList.Add(user);
            }
            return userList;
        }
        //对模型映射进行封装为私有方法ToModel
        private Users ToModel(DataRow row)
        {
            Users user = new Users();
            user.Id = (int)row["id"];
            user.Userno = row["userno"].ToString();
            user.Username = row["username"].ToString();
            user.Userlevel = (int)row["userlevel"];
            user.Password = row["password"].ToString();
            user.IsDelete = (bool)row["isdelete"];
            return user;
        }
    }
}
